﻿Public Class ClsTVoucherDbAccess
    Inherits ClsBaseDbAccess

    Public Sub New(ByVal s As String)
        MyBase.New(s, TABLE_NAME_T_VOUCHER)
    End Sub

    Public Sub New(ByVal s As String, ByVal n As String)
        MyBase.New(s, n)
    End Sub

    ''' <summary>
    ''' 指定会计期间的所有凭证反过账
    ''' </summary>
    ''' <param name="wp"></param>
    ''' <param name="ifPosted"></param>
    ''' <returns></returns>
    Public Function PostAndUnpostVoucherByWorkPeriod(ByVal wp As String, ByVal ifPosted As Boolean, Optional ByVal poster As String = "") As Integer

        Dim tmpRetInt As Integer = 0

        Try
            Dim tmpSql As String
            Dim tmpParas(2) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "Update [" & Me.AppTableName & "] set [Posted]=@Posted,[Poster]=@Poster "
            tmpSql &= " where [WorkPeriod]=@WorkPeriod "

            tmpParas(0) = New OleDb.OleDbParameter("@Posted", OleDb.OleDbType.Boolean)
            tmpParas(1) = New OleDb.OleDbParameter("@Poster", OleDb.OleDbType.VarWChar)
            tmpParas(2) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)

            tmpParas(0).Value = ifPosted
            tmpParas(1).Value = poster
            tmpParas(2).Value = wp

            tmpRetInt = tmpDbAccess.ExecuteSqlNoDataReturn(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - DeleteVoucher"

            '出现数据库错误
            tmpRetInt = -1

        End Try

        Return tmpRetInt

    End Function

    ''' <summary>
    ''' 批量过账或反过账标志写入
    ''' </summary>
    ''' <param name="tmpList"></param>
    ''' <param name="ifPosted"></param>
    ''' <returns></returns>
    Public Function PostVoucher(ByVal tmpList As List(Of ClsTVoucher), ByVal ifPosted As Boolean) As Integer

        Dim tmpRetInt As Integer = 0

        If tmpList Is Nothing Then
            Return 0
        End If

        Try
            Dim tmpSql As String
            Dim tmpParas(2) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            For Each tmpV As ClsTVoucher In tmpList

                '构造查询语句
                tmpSql = "Update [" & Me.AppTableName & "] set [Posted]=@Posted,[Poster]=@Poster "
                tmpSql &= " where [VoucherAutoId]=@VoucherAutoId "

                tmpParas(0) = New OleDb.OleDbParameter("@Posted", OleDb.OleDbType.Boolean)
                tmpParas(1) = New OleDb.OleDbParameter("@Poster", OleDb.OleDbType.VarWChar)
                tmpParas(2) = New OleDb.OleDbParameter("@VoucherAutoId", OleDb.OleDbType.Integer)

                tmpParas(0).Value = ifPosted
                If ifPosted Then
                    tmpParas(1).Value = tmpV.Poster
                Else
                    tmpParas(1).Value = ""
                End If
                tmpParas(2).Value = tmpV.VoucherAutoId

                tmpRetInt = tmpDbAccess.ExecuteSqlNoDataReturn(tmpSql, tmpParas)

                If tmpRetInt = 0 Then
                    Exit For
                End If
            Next

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - DeleteVoucher"

        End Try

        Return tmpRetInt

    End Function

    ''' <summary>
    ''' 凭证审核或消审
    ''' </summary>
    ''' <param name="tmpList"></param>
    ''' <param name="ifCheck"></param>
    ''' <returns></returns>
    Public Function CheckVoucher(ByVal tmpList As List(Of ClsTVoucher), ByVal ifCheck As Boolean) As Integer
        '批量审核或消审
        Dim tmpRetInt As Integer = 0

        If tmpList Is Nothing Then
            Return 0
        End If

        Try
            Dim tmpSql As String
            Dim tmpParas(2) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            For Each tmpV As ClsTVoucher In tmpList

                '构造查询语句
                tmpSql = "Update [" & Me.AppTableName & "] set [Checked]=@Checked,[Checker]=@Checker "
                tmpSql &= " where [VoucherAutoId]=@VoucherAutoId "

                tmpParas(0) = New OleDb.OleDbParameter("@Checked", OleDb.OleDbType.Boolean)
                tmpParas(1) = New OleDb.OleDbParameter("@Checker", OleDb.OleDbType.VarWChar)
                tmpParas(2) = New OleDb.OleDbParameter("@VoucherAutoId", OleDb.OleDbType.Integer)

                tmpParas(0).Value = ifCheck
                If ifCheck Then

                    tmpParas(1).Value = tmpV.Checker
                Else
                    tmpParas(1).Value = ""
                End If

                tmpParas(2).Value = tmpV.VoucherAutoId

                tmpRetInt = tmpDbAccess.ExecuteSqlNoDataReturn(tmpSql, tmpParas)

                If tmpRetInt = 0 Then
                    Exit For
                End If
            Next

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - DeleteVoucher"

        End Try

        Return tmpRetInt

    End Function

    Public Function DeleteVoucher(ByVal autoId As Integer) As Integer
        Dim tmpRetInt As Integer = 0

        Try
            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "Delete * from [" & Me.AppTableName & "] "
            tmpSql &= " where [VoucherAutoId]=@VoucherAutoId "
            tmpParas(0) = New OleDb.OleDbParameter("@VoucherGroup", OleDb.OleDbType.Integer)

            tmpParas(0).Value = autoId

            tmpRetInt = tmpDbAccess.ExecuteSqlNoDataReturn(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - DeleteVoucher"

        End Try

        Return tmpRetInt
    End Function

    ''' <summary>
    ''' 修改凭证中的凭证类别，主要是修改凭证字号
    ''' </summary>
    ''' <param name="tmpCls">凭证分组类对象ClsTVoucherGroup</param>
    ''' <returns>非0表示成功</returns>
    Public Function UpdateVoucherGroup(ByVal tmpCls As ClsTVoucherGroup) As Integer
        Dim tmpRetInt As Integer = 0

        Try
            Dim tmpSql As String
            Dim tmpParas(1) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "Update [" & Me.AppTableName & "] set [VoucherGroup]=@VoucherGroup where [VoucherGroupId]=@VoucherGroupId"
            tmpParas(0) = New OleDb.OleDbParameter("@VoucherGroup", OleDb.OleDbType.VarWChar)
            tmpParas(1) = New OleDb.OleDbParameter("@VoucherGroupId", OleDb.OleDbType.Integer)
            tmpParas(0).Value = tmpCls.GroupId
            tmpParas(1).Value = tmpCls.GroupName

            '查询并将返回数据解析为List对象
            tmpRetInt = tmpDbAccess.UpdateRecord(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - UpdateVoucherGroup"

        End Try

        Return tmpRetInt
    End Function

    ''' <summary>
    ''' 返回指定期间未过账凭证数量
    ''' </summary>
    ''' <param name="wp"></param>
    ''' <returns></returns>
    Public Function GetUnPostedCountInWorkPeriod(ByVal wp As String) As Integer

        Dim tmpRetInt As Integer = 0

        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "select count(*) as voucherCount from [" & Me.AppTableName & "] where [WorkPeriod]=@WorkPeriod and not [Posted]"
            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = wp

            '查询并将返回数据解析为List对象
            Integer.TryParse(tmpDbAccess.ExecuteSqlReturnUniqueField(tmpSql, tmpParas), tmpRetInt)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - GetUnCheckedCountInWorkPeriod"
        End Try

        Return tmpRetInt

    End Function

    ''' <summary>
    ''' 返回指定期间未审核凭证数量
    ''' </summary>
    ''' <param name="wp"></param>
    ''' <returns></returns>
    Public Function GetUnCheckedCountInWorkPeriod(ByVal wp As String) As Integer

        Dim tmpRetInt As Integer = 0

        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "select count(*) as voucherCount from [" & Me.AppTableName & "] where [WorkPeriod]=@WorkPeriod and not [Checked]"
            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = wp

            '查询并将返回数据解析为List对象
            Integer.TryParse(tmpDbAccess.ExecuteSqlReturnUniqueField(tmpSql, tmpParas), tmpRetInt)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - GetUnCheckedCountInWorkPeriod"
        End Try

        Return tmpRetInt

    End Function

    ''' <summary>
    ''' 返回指定期间的凭证数量
    ''' </summary>
    ''' <param name="wp"></param>
    ''' <returns></returns>
    Public Function GetVoucherCountInWorkPeriod(ByVal wp As String) As Integer

        Dim tmpRetInt As Integer = 0

        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "select count(*) as voucherCount from [" & Me.AppTableName & "] where [WorkPeriod]=@WorkPeriod "
            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = wp

            '查询并将返回数据解析为List对象
            Integer.TryParse(tmpDbAccess.ExecuteSqlReturnUniqueField(tmpSql, tmpParas), tmpRetInt)

            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - GetVoucherCountInWorkPeriod"
        End Try

        Return tmpRetInt

    End Function

    ''' <summary>
    ''' 返回指定期间的贷方合计
    ''' </summary>
    ''' <param name="wp"></param>
    ''' <returns></returns>
    Public Function GetDebitSumInWorkPeriod(ByVal wp As String) As Decimal

        Dim tmpRetDec As Decimal
        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select sum(SumOfDebit) as CreditSum from [" & Me.AppTableName & "] where [WorkPeriod]=@WorkPeriod "
            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = wp

            '查询并将返回数据解析为List对象
            If Not Decimal.TryParse(tmpDbAccess.ExecuteSqlReturnUniqueField(tmpSql, tmpParas), tmpRetDec) Then
                '转换失败
            End If


            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - GetDebitSumInWorkPeriod"

        End Try

        Return tmpRetDec

    End Function

    ''' <summary>
    ''' 返回指定期间的借方合计
    ''' </summary>
    ''' <param name="wp"></param>
    ''' <returns></returns>
    Public Function GetCreditSumInWorkPeriod(ByVal wp As String) As Decimal

        Dim tmpRetDec As Decimal
        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select sum(SumOfCredit) as CreditSum from [" & Me.AppTableName & "] where [WorkPeriod]=@WorkPeriod "
            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = wp

            '查询并将返回数据解析为List对象
            If Not Decimal.TryParse(tmpDbAccess.ExecuteSqlReturnUniqueField(tmpSql, tmpParas), tmpRetDec) Then
                '转换失败
            End If


            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - GetCreditSumInWorkPeriod"

        End Try

        Return tmpRetDec

    End Function

    ''' <summary>
    ''' 返回未过账的凭证集合
    ''' </summary>
    ''' <param name="wp">指定会计期间</param>
    ''' <returns></returns>
    Public Function GetUnPostVoucherInWorkPeriod(ByVal wp As String) As List(Of ClsTVoucher)

        Dim tmpList As List(Of ClsTVoucher) = New List(Of ClsTVoucher)

        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection() 'New ClsDbAccessMdb(mConnectionString)

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [" & Me.AppTableName & "] where [WorkPeriod]=@WorkPeriod and not [Posted] "
            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = wp

            '查询并将返回数据解析为List对象
            tmpList = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql, tmpParas)), List(Of ClsTVoucher))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpList

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - GetAllVoucherInWorkPeriod"

            Return tmpList
        End Try

    End Function

    ''' <summary>
    ''' 返回指定会计期间的全部凭证。
    ''' 如果没有数据，则返回一个空的集合
    ''' </summary>
    ''' <param name="wp">会计期间：202102</param>
    ''' <returns>凭证列表集合</returns>
    Public Function GetAllVoucherInWorkPeriod(ByVal wp As String) As List(Of ClsTVoucher)

        Dim tmpList As List(Of ClsTVoucher) = New List(Of ClsTVoucher)
        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection() 'New ClsDbAccessMdb(mConnectionString)

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select * from [" & Me.AppTableName & "] where [WorkPeriod]=@WorkPeriod "
            tmpParas(0) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(0).Value = wp

            '查询并将返回数据解析为List对象
            tmpList = CType(ConvertRecordToClass(tmpDbAccess.ExecuteSqlReturnDataSet(tmpSql, tmpParas)), List(Of ClsTVoucher))

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpList

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - GetAllVoucherInWorkPeriod"

            Return tmpList
        End Try

    End Function

    ''' <summary>
    ''' 返回指定凭证组别是否被使用过
    ''' </summary>
    ''' <param name="tmpCls"></param>
    ''' <returns></returns>
    Public Function GetIfHaveVoucherGroup(ByVal tmpCls As ClsTVoucherGroup) As Integer
        Dim tmpRetInt As Integer
        Try

            Dim tmpSql As String
            Dim tmpParas(0) As OleDb.OleDbParameter

            Dim tmpDbAccess = GetConnection()

            '打开数据库
            tmpDbAccess.DbOpen()
            '构造查询语句
            tmpSql = "select count(VoucherGroupId) as vgCount from [" & Me.AppTableName & "] where [VoucherGroupId]=@VoucherGroupId "
            tmpParas(0) = New OleDb.OleDbParameter("@VoucherGroupId", OleDb.OleDbType.Integer)
            tmpParas(0).Value = tmpCls.GroupId

            '查询并将返回数据解析为List对象
            tmpRetInt = CType(tmpDbAccess.ExecuteSqlReturnUniqueField(tmpSql, tmpParas), Integer)


            '关闭数据库
            tmpDbAccess.DbClose()

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - GetIfHaveVoucherGroup"

        End Try

        Return tmpRetInt
    End Function

    ''' <summary>
    ''' 返回用户数据库内是否有凭证
    ''' </summary>
    ''' <returns></returns>
    Public Function GetIfHaveVoucher() As Boolean

        Try

            Dim tmpList As List(Of ClsTVoucher) = GetAllRecords(Of ClsTVoucher)()

            If tmpList.Count > 0 Then
                Return True
            Else
                Return False
            End If

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - GetIfHaveVoucher"

            Return Nothing
        End Try


    End Function

    Public Function UpdateVoucher(ByVal tmpCls As ClsTVoucher) As Integer
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()

            Dim tmpParas(21) As OleDb.OleDbParameter
            Dim tmpReturn As Integer

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "Update [" & Me.AppTableName & "] set "
            tmpSql &= "[VoucherId]=@VoucherId,[WorkPeriod]=@WorkPeriod,[VoucherGroupId]=@VoucherGroupId,"
            tmpSql &= "[VoucherGroup]=@VoucherGroup,[VoucherNumber]=@VoucherNumber,[VoucherDate]=@VoucherDate,"
            tmpSql &= "[Attachment]=@Attachment,[Casher]=@Casher,[Poster]=@Poster,[Maker]=@Maker,"
            tmpSql &= "[Checker]=@Checker,[Hander]=@Hander,[SumOfDebit]=@SumOfDebit,"
            tmpSql &= "[SumOfCredit]=@SumOfCredit,[VoucherStatus]=@VoucherStatus,[Posted]=@Posted,"
            tmpSql &= "[Module]=@Module,[Deleted]=@Deleted,[Memo]=@Memo,[MoneyHz]=@MoneyHz,[Checked]=@Checked "
            tmpSql &= " where [VoucherAutoId]=@VoucherAutoId "

            '构造参数集合
            tmpParas(0) = New OleDb.OleDbParameter("@VoucherId", OleDb.OleDbType.Integer)
            tmpParas(1) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(2) = New OleDb.OleDbParameter("@VoucherGroupId", OleDb.OleDbType.Integer)
            tmpParas(3) = New OleDb.OleDbParameter("@VoucherGroup", OleDb.OleDbType.VarWChar)
            tmpParas(4) = New OleDb.OleDbParameter("@VoucherNumber", OleDb.OleDbType.Integer)
            tmpParas(5) = New OleDb.OleDbParameter("@VoucherDate", OleDb.OleDbType.Date)
            tmpParas(6) = New OleDb.OleDbParameter("@Attachment", OleDb.OleDbType.Integer)
            tmpParas(7) = New OleDb.OleDbParameter("@Casher", OleDb.OleDbType.VarWChar)
            tmpParas(8) = New OleDb.OleDbParameter("@Poster", OleDb.OleDbType.VarWChar)
            tmpParas(9) = New OleDb.OleDbParameter("@Maker", OleDb.OleDbType.VarWChar)
            tmpParas(10) = New OleDb.OleDbParameter("@Checker", OleDb.OleDbType.VarWChar)
            tmpParas(11) = New OleDb.OleDbParameter("@Hander", OleDb.OleDbType.VarWChar)
            tmpParas(12) = New OleDb.OleDbParameter("@SumOfDebit", OleDb.OleDbType.Decimal)
            tmpParas(13) = New OleDb.OleDbParameter("@SumOfCredit", OleDb.OleDbType.Decimal)
            tmpParas(14) = New OleDb.OleDbParameter("@VoucherStatus", OleDb.OleDbType.VarWChar)
            tmpParas(15) = New OleDb.OleDbParameter("@Posted", OleDb.OleDbType.Boolean)
            tmpParas(16) = New OleDb.OleDbParameter("@Module", OleDb.OleDbType.VarWChar)
            tmpParas(17) = New OleDb.OleDbParameter("@Deleted", OleDb.OleDbType.Integer)
            tmpParas(18) = New OleDb.OleDbParameter("@Memo", OleDb.OleDbType.VarWChar)
            tmpParas(19) = New OleDb.OleDbParameter("@MoneyHz", OleDb.OleDbType.VarWChar)
            tmpParas(20) = New OleDb.OleDbParameter("@Checked", OleDb.OleDbType.Boolean)
            tmpParas(21) = New OleDb.OleDbParameter("@VoucherAutoId", OleDb.OleDbType.Integer)

            '为查询参数赋值
            tmpParas(0).Value = tmpCls.VoucherId
            tmpParas(1).Value = tmpCls.WorkPeriod
            tmpParas(2).Value = tmpCls.VoucherGroupId
            tmpParas(3).Value = tmpCls.VoucherGroup
            tmpParas(4).Value = tmpCls.VoucherNumber
            tmpParas(5).Value = tmpCls.VoucherDate
            tmpParas(6).Value = tmpCls.Attachment
            tmpParas(7).Value = tmpCls.Casher
            tmpParas(8).Value = tmpCls.Poster
            tmpParas(9).Value = tmpCls.Maker
            tmpParas(10).Value = tmpCls.Checker
            tmpParas(11).Value = tmpCls.Hander
            tmpParas(12).Value = tmpCls.SumOfDebit
            tmpParas(13).Value = tmpCls.SumOfCredit
            tmpParas(14).Value = tmpCls.VoucherStatus
            tmpParas(15).Value = tmpCls.Posted
            tmpParas(16).Value = tmpCls.Module
            tmpParas(17).Value = tmpCls.Deleted
            tmpParas(18).Value = tmpCls.Memo
            tmpParas(19).Value = tmpCls.MoneyHz
            tmpParas(20).Value = tmpCls.Checked
            tmpParas(21).Value = tmpCls.VoucherAutoId

            '执行更新查询(插入、修改、删除)
            tmpReturn = tmpDbAccess.UpdateRecord(tmpSql, tmpParas)

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - UpdateVoucher"

            Return 0
        End Try
    End Function

    ''' <summary>
    ''' 保存凭证主体
    ''' </summary>
    ''' <param name="tmpCls"></param>
    ''' <returns></returns>
    Public Function InsertVoucher(ByVal tmpCls As ClsTVoucher) As Integer
        Try

            Dim tmpSql As String
            Dim tmpDbAccess = GetConnection()

            Dim tmpParas(20) As OleDb.OleDbParameter
            Dim tmpReturn As Integer

            '打开数据库
            tmpDbAccess.DbOpen()

            '构造查询语句
            tmpSql = "insert into [" & Me.AppTableName & "] ("
            tmpSql &= "[VoucherId],[WorkPeriod],[VoucherGroupId],[VoucherGroup],[VoucherNumber],"
            tmpSql &= "[VoucherDate],[Attachment],[Casher],[Poster],[Maker],[Checker],[Hander],"
            tmpSql &= "[SumOfDebit],[SumOfCredit],[VoucherStatus],[Posted],[Module],"
            tmpSql &= "[Deleted],[Memo],[MoneyHz],[Checked]"
            tmpSql &= ") values("
            tmpSql &= "@VoucherId,@WorkPeriod,@VoucherGroupId,@VoucherGroup,@VoucherNumber,"
            tmpSql &= "@VoucherDate,@Attachment,@Casher,@Poster,@Maker,@Checker,@Hander,"
            tmpSql &= "@SumOfDebit,@SumOfCredit,@VoucherStatus,@Posted,@Module,"
            tmpSql &= "@Deleted,@Memo,@MoneyHz,@Checked"
            tmpSql &= ")"

            '构造参数集合
            tmpParas(0) = New OleDb.OleDbParameter("@VoucherId", OleDb.OleDbType.Integer)
            tmpParas(1) = New OleDb.OleDbParameter("@WorkPeriod", OleDb.OleDbType.VarWChar)
            tmpParas(2) = New OleDb.OleDbParameter("@VoucherGroupId", OleDb.OleDbType.Integer)
            tmpParas(3) = New OleDb.OleDbParameter("@VoucherGroup", OleDb.OleDbType.VarWChar)
            tmpParas(4) = New OleDb.OleDbParameter("@VoucherNumber", OleDb.OleDbType.Integer)
            tmpParas(5) = New OleDb.OleDbParameter("@VoucherDate", OleDb.OleDbType.Date)
            tmpParas(6) = New OleDb.OleDbParameter("@Attachment", OleDb.OleDbType.Integer)
            tmpParas(7) = New OleDb.OleDbParameter("@Casher", OleDb.OleDbType.VarWChar)
            tmpParas(8) = New OleDb.OleDbParameter("@Poster", OleDb.OleDbType.VarWChar)
            tmpParas(9) = New OleDb.OleDbParameter("@Maker", OleDb.OleDbType.VarWChar)
            tmpParas(10) = New OleDb.OleDbParameter("@Checker", OleDb.OleDbType.VarWChar)
            tmpParas(11) = New OleDb.OleDbParameter("@Hander", OleDb.OleDbType.VarWChar)
            tmpParas(12) = New OleDb.OleDbParameter("@SumOfDebit", OleDb.OleDbType.Decimal)
            tmpParas(13) = New OleDb.OleDbParameter("@SumOfCredit", OleDb.OleDbType.Decimal)
            tmpParas(14) = New OleDb.OleDbParameter("@VoucherStatus", OleDb.OleDbType.VarWChar)
            tmpParas(15) = New OleDb.OleDbParameter("@Posted", OleDb.OleDbType.Boolean)
            tmpParas(16) = New OleDb.OleDbParameter("@Module", OleDb.OleDbType.VarWChar)
            tmpParas(17) = New OleDb.OleDbParameter("@Deleted", OleDb.OleDbType.Integer)
            tmpParas(18) = New OleDb.OleDbParameter("@Memo", OleDb.OleDbType.VarWChar)
            tmpParas(19) = New OleDb.OleDbParameter("@MoneyHz", OleDb.OleDbType.VarWChar)
            tmpParas(20) = New OleDb.OleDbParameter("@Checked", OleDb.OleDbType.Boolean)

            '为查询参数赋值
            tmpParas(0).Value = tmpCls.VoucherId
            tmpParas(1).Value = tmpCls.WorkPeriod
            tmpParas(2).Value = tmpCls.VoucherGroupId
            tmpParas(3).Value = tmpCls.VoucherGroup
            tmpParas(4).Value = tmpCls.VoucherNumber
            tmpParas(5).Value = tmpCls.VoucherDate
            tmpParas(6).Value = tmpCls.Attachment
            tmpParas(7).Value = tmpCls.Casher
            tmpParas(8).Value = tmpCls.Poster
            tmpParas(9).Value = tmpCls.Maker
            tmpParas(10).Value = tmpCls.Checker
            tmpParas(11).Value = tmpCls.Hander
            tmpParas(12).Value = tmpCls.SumOfDebit
            tmpParas(13).Value = tmpCls.SumOfCredit
            tmpParas(14).Value = tmpCls.VoucherStatus
            tmpParas(15).Value = tmpCls.Posted
            tmpParas(16).Value = tmpCls.Module
            tmpParas(17).Value = tmpCls.Deleted
            tmpParas(18).Value = tmpCls.Memo
            tmpParas(19).Value = tmpCls.MoneyHz
            tmpParas(20).Value = tmpCls.Checked

            '执行更新查询(插入、修改、删除)
            tmpReturn = tmpDbAccess.InsertData(tmpSql, tmpParas, True)

            '关闭数据库
            tmpDbAccess.DbClose()

            Return tmpReturn

        Catch ex As OleDb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - InsertVoucher"

            Return 0
        End Try
    End Function

    Public Overrides Function ConvertRecordToClass(reader As OleDb.OleDbDataReader) As Object
        If reader Is Nothing Then
            Return Nothing
        End If
        Dim tmpList As List(Of ClsTVoucher) = New List(Of ClsTVoucher)

        Try

            Dim tmpCls As ClsTVoucher

            While reader.Read()

                tmpCls = New ClsTVoucher()

                With tmpCls

                    .VoucherAutoId = reader.GetInt32(0)
                    .VoucherId = reader.GetInt32(1)
                    .WorkPeriod = reader.GetString(2)
                    .VoucherGroupId = reader.GetInt32(3)
                    .VoucherGroup = reader.GetString(4)
                    .VoucherNumber = reader.GetInt32(5)
                    .VoucherDate = reader.GetDateTime(6) '凭证日期：不能为空
                    .Attachment = reader.GetInt16(7)
                    .Casher = ConvertDbNullString(reader(8))
                    .Poster = ConvertDbNullString(reader(9))
                    .Maker = ConvertDbNullString(reader(10))
                    .Checker = ConvertDbNullString(reader(11))
                    .Hander = ConvertDbNullString(reader(12))
                    .SumOfDebit = reader.GetDecimal(13)
                    .SumOfCredit = reader.GetDecimal(14)
                    .VoucherStatus = reader.GetString(15) '状态：正常或红冲，不能为空
                    .Posted = reader.GetBoolean(16) '是否过账
                    .Module = ConvertDbNullString(reader(17)) '种类：普通 、 自动、 损益 、资产
                    .Deleted = reader.GetBoolean(18) '是否删除
                    .Memo = ConvertDbNullString(reader(19))
                    .MoneyHz = ConvertDbNullString(reader(20))
                    .Checked = reader.GetBoolean(21)

                    .EntryList = New List(Of ClsTVoucherDetail)() '一个空的分录集合，避免检查null值

                End With

                tmpList.Add(tmpCls)

            End While

            Return tmpList

        Catch ex As oledb.OleDbException

            mErrorNumber = ex.ErrorCode
            mErrorMessage = ex.Message
            mErrorCreater = "ClsTVoucherDbAccess - ConvertRecordToClass"

            Return Nothing
        End Try
    End Function
End Class
